using System;
using System.Collections.Generic;
using Oracle.DataAccess.Client;
using System.Data;

namespace MappingGenerator
{
    public class OracleSchemaReader : IDbSchemaReader
    {
        private string _connectionString ;
        public OracleSchemaReader(string connectionString)
        {
            _connectionString = connectionString;
        }

        public TableInfo GetTable(string tableName)
        {
            TableInfo table = new TableInfo();
            using (var connection = new OracleConnection(_connectionString))
            {
                connection.Open();
                using (var command = new OracleCommand())
                {
                    command.Connection = connection;
                    var sql = @"select C.COLUMN_NAME,C.DATA_TYPE,C.DATA_LENGTH,C.NULLABLE,
                                CN.CONSTRAINT_TYPE
                                from all_tab_columns C
                                left outer join all_cons_columns K on K.table_name = C.TABLE_NAME
                                                                        and K.COLUMN_NAME = C.COLUMN_NAME
                                left outer join all_constraints CN on K.CONSTRAINT_NAME = CN.CONSTRAINT_NAME                                   
                                where C.TABLE_name  = '{0}'";
                    command.CommandText = string.Format(sql, tableName);
                    
                    using (var reader = command.ExecuteReader())
                    {
                        table = new TableInfo {Name = tableName};
                        while (reader.Read())
                        {
                            table.Columns.Add(ReadColumn(reader));
                        }
                    }
                }
            }
            return table;
        }
        public ColumInfo ReadColumn(IDataReader reader)
        {
            var info = new ColumInfo();
            info.Name = reader["COLUMN_NAME"].ToString();
            info.DataType = reader["DATA_TYPE"].ToString();
            info.Nullable = reader["NULLABLE"].ToString().Equals("N", StringComparison.InvariantCultureIgnoreCase);
            info.MemberType = GetMemberType(info.DataType);
            if (DBNull.Value != reader["CONSTRAINT_TYPE"])
            {
                string constraintType = reader["CONSTRAINT_TYPE"].ToString();
                if (constraintType.Equals("p", StringComparison.InvariantCultureIgnoreCase))
                {
                    info.PrimaryKey = true;
                }
            }
            return info;
        }
        public List<string> GetTablesNames(string owner =null)
        {
            var tableNames = new List<string>();
            string sql = null;
            if (string.IsNullOrEmpty(owner))
                sql = @"select table_name from all_tables";
            else
                sql = string.Format(@"select table_name from all_tables where owner = '{0}'", owner);

            using (var connection = new OracleConnection(_connectionString))
            {
                connection.Open();
                using (var command = new OracleCommand())
                {
                    command.Connection = connection;
                    command.CommandText = sql;

                    using (var reader = command.ExecuteReader())
                    {
                        
                        while (reader.Read())
                        {
                            tableNames.Add(reader["table_name"].ToString());
                        }
                    }
                }
            }
            return tableNames;
        }
        private string GetMemberType(string dataType)
        {
            switch (dataType.ToUpper())
            {
                case "INTEGER":
                case "INT":
                case "SMALLINT":
                case "PLS_INTEGER":
                    return "int";

                case "NUMBER":
                case "FLOAT":
                    return "decimal";

                case "VARCHAR2":
                case "NVARCHAR2":
                case "NCHAR":
                case "CHAR":
                    return "string";
                
                case "ROWID":
                case "UROWID":
                    return "guid";

                default:
                    return "string";
            }
        }
    }
}